﻿using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CK.Sprite.Core
{
    public class FormMysqlCore
    {
        public string CreateSqlWhere(JToken sqlWheres, JObject tempParamValues)
        {
            if (sqlWheres == null)
            {
                return "1=1";
            }
            StringBuilder sbSqlWhere = new StringBuilder();
            sbSqlWhere.Append("1=1");

            ExpressSqlModel expressSqlModel = sqlWheres.ToObject<ExpressSqlModel>();
            if (expressSqlModel != null && expressSqlModel.Children != null && expressSqlModel.Children.Count > 0)
            {
                string strSql = ExpressSqlHelper.CreateSqlWhere(expressSqlModel, tempParamValues, CreateConditionSql);
                if (!string.IsNullOrEmpty(strSql))
                {
                    sbSqlWhere.Append($" AND {strSql}");
                }
            }

            return sbSqlWhere.ToString();
        }

        public string CreateSqlWhereArray(JArray sqlWheres, JObject tempParamValues)
        {
            StringBuilder sbSqlWhere = new StringBuilder();
            sbSqlWhere.Append("1=1");
            if (sqlWheres == null || sqlWheres.Count == 0)
            {
                return sbSqlWhere.ToString();
            }

            int index = 1;
            foreach (var sqlWhere in sqlWheres)
            {
                var conditionType = sqlWhere["conditionType"].ToObject<EConditionType>();
                var field = sqlWhere["field"].ToString();
                switch (conditionType)
                {
                    case EConditionType.等于:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}=@SW{index}_{field}");
                        tempParamValues.Add(new JProperty($"SW{index}_{field}", sqlWhere["value"].ToObject<object>()));
                        break;
                    case EConditionType.Like:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark} LIKE CONCAT('%',@SW{index}_{field},'%')");
                        tempParamValues.Add(new JProperty($"SW{index}_{field}", sqlWhere["value"].ToObject<object>()));
                        break;
                    case EConditionType.In:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark} IN @SW{index}_{field}");
                        tempParamValues.Add(new JProperty($"SW{index}_{field}", sqlWhere["value"].ToObject<object>()));
                        break;
                    case EConditionType.Between:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}  BETWEEN @SW{index}_{field}_1 AND @SW{index}_{field}_2");
                        var inValues = sqlWhere["value"].ToArray();
                        tempParamValues.Add(new JProperty($"SW{index}_{field}_1", inValues[0].ToObject<object>()));
                        tempParamValues.Add(new JProperty($"SW{index}_{field}_2", inValues[1].ToObject<object>()));
                        break;
                    case EConditionType.大于:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}>@SW{index}_{field}");
                        tempParamValues.Add(new JProperty($"SW{index}_{field}", sqlWhere["value"].ToObject<object>()));
                        break;
                    case EConditionType.大于等于:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}>=@SW{index}_{field}");
                        tempParamValues.Add(new JProperty($"SW{index}_{field}", sqlWhere["value"].ToObject<object>()));
                        break;
                    case EConditionType.小于:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}<@SW{index}_{field}");
                        tempParamValues.Add(new JProperty($"SW{index}_{field}", sqlWhere["value"].ToObject<object>()));
                        break;
                    case EConditionType.小于等于:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}<=@SW{index}_{field}");
                        tempParamValues.Add(new JProperty($"SW{index}_{field}", sqlWhere["value"].ToObject<object>()));
                        break;
                    case EConditionType.不等于:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}<>@SW{index}_{field}");
                        tempParamValues.Add(new JProperty($"SW{index}_{field}", sqlWhere["value"].ToObject<object>()));
                        break;
                    case EConditionType.Null:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark} IS NULL");
                        break;
                    case EConditionType.NotNull:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark} IS NOT NULL");
                        break;
                    case EConditionType.NotIn:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark} NOT IN @SW{index}_{field}");
                        tempParamValues.Add(new JProperty($"SW{index}_{field}", sqlWhere["value"].ToObject<object>()));
                        break;
                    default:
                        break;
                }

                index++;
            }

            return sbSqlWhere.ToString();
        }

        public string CreateConditionSql(JObject sqlWhereParamValues, ExpressSqlModel expressSqlModel, ref int index)
        {
            var conditionType = expressSqlModel.ConditionType;
            var field = expressSqlModel.Field;
            StringBuilder sbSqlWhere = new StringBuilder();
            switch (conditionType)
            {
                case EConditionType.等于:
                    sbSqlWhere.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}=@SW{index}_{field}");
                    sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
                    break;
                case EConditionType.Like:
                    sbSqlWhere.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark} LIKE CONCAT('%',@SW{index}_{field},'%')");
                    sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
                    break;
                case EConditionType.In:
                    var inValues = expressSqlModel.Value as JArray;
                    if (inValues.Count > 0)
                    {
                        sbSqlWhere.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark} IN @SW{index}_{field}");
                        sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
                    }
                    else
                    {
                        sbSqlWhere.Append($"1=1");
                        index--;
                    }
                    break;
                case EConditionType.Between:
                    var betweenValues = expressSqlModel.Value as JArray;
                    if (betweenValues.Count > 1)
                    {
                        sbSqlWhere.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark} BETWEEN @SW{index}_{field}_1 AND @SW{index}_{field}_2");
                        sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}_1", betweenValues[0]));
                        sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}_2", betweenValues[1]));
                    }
                    else
                    {
                        sbSqlWhere.Append($"1=1");
                        index--;
                    }
                    break;
                case EConditionType.大于:
                    sbSqlWhere.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}>@SW{index}_{field}");
                    sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
                    break;
                case EConditionType.大于等于:
                    sbSqlWhere.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}>=@SW{index}_{field}");
                    sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
                    break;
                case EConditionType.小于:
                    sbSqlWhere.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}<@SW{index}_{field}");
                    sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
                    break;
                case EConditionType.小于等于:
                    sbSqlWhere.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}<=@SW{index}_{field}");
                    sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
                    break;
                case EConditionType.不等于:
                    sbSqlWhere.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}<>@SW{index}_{field}");
                    sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
                    break;
                case EConditionType.Null:
                    sbSqlWhere.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark} IS NULL");
                    break;
                case EConditionType.NotNull:
                    sbSqlWhere.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark} IS NOT NULL");
                    break;
                case EConditionType.NotIn:
                    var notInValues = expressSqlModel.Value as JArray;
                    if (notInValues.Count > 0)
                    {
                        sbSqlWhere.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark} NOT IN @SW{index}_{field}");
                        sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
                    }
                    else
                    {
                        sbSqlWhere.Append($"1=1");
                        index--;
                    }
                    break;
                default:
                    break;
            }

            index++;

            return sbSqlWhere.ToString();
        }
    }

    public class MysqlConsts
    {
        internal const string PreMark = "`";
        internal const string PostMark = "`";
    }
}
